
---------------------------------- Tables need to be edited -----------------------------------

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_LOG]') AND TYPE IN (N'U'))
CREATE TABLE REG_LOG (
             REG_LOG_ID INTEGER IDENTITY(1,1),
             REG_PATH VARCHAR(2000),
             REG_USER_ID VARCHAR(31) NOT NULL,
             REG_LOGGED_TIME TIMESTAMP  NOT NULL,
             REG_ACTION INTEGER NOT NULL,
             REG_ACTION_DATA VARCHAR(500),
             REG_TENANT_ID INTEGER DEFAULT 0,
             CONSTRAINT PK_REG_LOG PRIMARY KEY (REG_LOG_ID, REG_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_PATH]') AND TYPE IN (N'U'))
CREATE TABLE REG_PATH(
             REG_PATH_ID INTEGER IDENTITY(1,1) ,
             REG_PATH_VALUE VARCHAR(2000) NOT NULL,
             REG_PATH_PARENT_ID INTEGER,
             REG_TENANT_ID INTEGER DEFAULT 0,
             CONSTRAINT PK_PATH PRIMARY KEY(REG_PATH_ID, REG_TENANT_ID),
             CONSTRAINT UNIQUE_REG_PATH_TENANT_ID UNIQUE (REG_PATH_VALUE,REG_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_CONTENT]') AND TYPE IN (N'U'))
CREATE TABLE REG_CONTENT (
             REG_CONTENT_ID INTEGER IDENTITY(1,1) ,
             REG_CONTENT_DATA VARBINARY(MAX),
             REG_TENANT_ID INTEGER DEFAULT 0,
             CONSTRAINT PK_REG_CONTENT PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE]') AND TYPE IN (N'U'))
CREATE TABLE REG_RESOURCE (
            REG_PATH_ID         INTEGER NOT NULL IDENTITY(1,1),
            REG_NAME            VARCHAR(256),
            REG_VERSION         INTEGER,
            REG_MEDIA_TYPE      VARCHAR(500),
            REG_CREATOR         VARCHAR(31) NOT NULL,
            REG_CREATED_TIME    TIMESTAMP NOT NULL,
            REG_LAST_UPDATOR    VARCHAR(31),
            REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL,
            REG_DESCRIPTION     VARCHAR(1000),
            REG_CONTENT_ID      INTEGER,
            REG_TENANT_ID INTEGER DEFAULT 0,
            REG_UUID VARCHAR(100) NOT NULL,
            CONSTRAINT FK_REG_RES_PATH FOREIGN KEY (REG_PATH_ID,REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID,REG_TENANT_ID),         
            CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_COMMENT]') AND TYPE IN (N'U'))
CREATE TABLE REG_COMMENT (
            REG_ID        INTEGER, IDENTITY(1,1)
            REG_COMMENT_TEXT      VARCHAR(500) NOT NULL,
            REG_USER_ID           VARCHAR(31) NOT NULL,
            REG_COMMENTED_TIME    TIMESTAMP NOT NULL,
            REG_TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT PK_REG_COMMENT PRIMARY KEY(REG_ID, REG_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RATING]') AND TYPE IN (N'U'))
CREATE TABLE REG_RATING (
            REG_ID     INTEGER IDENTITY(1,1),
            REG_RATING        INTEGER NOT NULL,
            REG_USER_ID       VARCHAR(31) NOT NULL,
            REG_RATED_TIME    TIMESTAMP NOT NULL,
            REG_TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT PK_REG_RATING PRIMARY KEY(REG_ID, REG_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_TAG]') AND TYPE IN (N'U'))
CREATE TABLE REG_TAG (
            REG_ID         INTEGER IDENTITY(1,1),
            REG_TAG_NAME       VARCHAR(500) NOT NULL,
            REG_USER_ID        VARCHAR(31) NOT NULL,
            REG_TAGGED_TIME    TIMESTAMP NOT NULL,
            REG_TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT PK_REG_TAG PRIMARY KEY(REG_ID, REG_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_PROPERTY]') AND TYPE IN (N'U'))
CREATE TABLE REG_PROPERTY (
            REG_ID         INTEGER IDENTITY(1,1),
            REG_NAME       VARCHAR(100) NOT NULL,
            REG_VALUE        VARCHAR(1000),
            REG_TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT PK_REG_PROPERTY PRIMARY KEY(REG_ID, REG_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_ASSOCIATION]') AND TYPE IN (N'U'))
CREATE TABLE REG_ASSOCIATION (
            REG_ASSOCIATION_ID INTEGER IDENTITY(1,1),
            REG_SOURCEPATH VARCHAR (2000) NOT NULL,
            REG_TARGETPATH VARCHAR (2000) NOT NULL,
            REG_ASSOCIATION_TYPE VARCHAR (2000) NOT NULL,
            REG_TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT PK_REG_ASSOCIATION PRIMARY KEY (REG_ASSOCIATION_ID, REG_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_SNAPSHOT]') AND TYPE IN (N'U'))
CREATE TABLE REG_SNAPSHOT (
            REG_SNAPSHOT_ID     INTEGER IDENTITY(1,1),
            REG_PATH_ID            INTEGER NOT NULL,
            REG_RESOURCE_NAME      VARCHAR(256),
            REG_RESOURCE_VIDS     VARBINARY(MAX) NOT NULL,
            REG_TENANT_ID INTEGER DEFAULT 0,
            FOREIGN KEY (REG_PATH_ID,REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID,REG_TENANT_ID),
            CONSTRAINT PK_REG_SNAPSHOT PRIMARY KEY(REG_SNAPSHOT_ID, REG_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_TENANT]') AND TYPE IN (N'U'))
CREATE TABLE UM_TENANT (
                    UM_ID INTEGER IDENTITY(1,1),
                    UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
                    UM_EMAIL VARCHAR(255),
                    UM_ACTIVE NUMBER(1) DEFAULT 0,
                    UM_CREATED_DATE TIMESTAMP NOT NULL,
                    UM_USER_CONFIG VARBINARY(MAX),
                    PRIMARY KEY (UM_ID),
                    UNIQUE(UM_DOMAIN_NAME)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_DOMAIN]') AND TYPE IN (N'U'))
CREATE TABLE UM_DOMAIN(
            UM_DOMAIN_ID INTEGER NOT NULL IDENTITY(1,1),
            UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
            UM_TENANT_ID INTEGER DEFAULT 0,
            PRIMARY KEY (UM_DOMAIN_ID, UM_TENANT_ID),
            UNIQUE(UM_DOMAIN_NAME,UM_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_USER]') AND TYPE IN (N'U'))
CREATE TABLE UM_USER (
                    UM_ID INTEGER IDENTITY(1,1),
                    UM_USER_NAME VARCHAR(255) NOT NULL,
                    UM_USER_PASSWORD VARCHAR(255) NOT NULL,
                    UM_SALT_VALUE VARCHAR(31),
                    UM_REQUIRE_CHANGE NUMBER(1) DEFAULT 0,
                    UM_CHANGED_TIME TIMESTAMP NOT NULL,
                    UM_TENANT_ID INTEGER DEFAULT 0,
                    PRIMARY KEY (UM_ID, UM_TENANT_ID),
                    UNIQUE(UM_USER_NAME, UM_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_SYSTEM_USER]') AND TYPE IN (N'U'))
CREATE TABLE UM_SYSTEM_USER (
            UM_ID INTEGER IDENTITY(1,1),
            UM_USER_NAME VARCHAR(255) NOT NULL,
            UM_USER_PASSWORD VARCHAR(255) NOT NULL,
            UM_SALT_VALUE VARCHAR(31),
            UM_REQUIRE_CHANGE NUMBER(1) DEFAULT 0,
                    UM_CHANGED_TIME TIMESTAMP NOT NULL,
            UM_TENANT_ID INTEGER DEFAULT 0,
            PRIMARY KEY (UM_ID, UM_TENANT_ID),
            UNIQUE(UM_USER_NAME, UM_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_USER_ATTRIBUTE]') AND TYPE IN (N'U'))
CREATE TABLE UM_USER_ATTRIBUTE (
                    UM_ID INTEGER IDENTITY(1,1),
                    UM_ATTR_NAME VARCHAR(255) NOT NULL,
                    UM_ATTR_VALUE VARCHAR(255),
                    UM_PROFILE_ID VARCHAR(255),
                    UM_USER_ID INTEGER,
                    UM_TENANT_ID INTEGER DEFAULT 0,
                    FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID),
                    PRIMARY KEY (UM_ID, UM_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_ROLE]') AND TYPE IN (N'U'))
CREATE TABLE UM_ROLE (
                    UM_ID INTEGER IDENTITY(1,1),
                    UM_ROLE_NAME VARCHAR(255) NOT NULL,
                    UM_TENANT_ID INTEGER DEFAULT 0,
                    UM_SHARED_ROLE CHAR(1) DEFAULT 0,
                    PRIMARY KEY (UM_ID, UM_TENANT_ID),
                    UNIQUE(UM_ROLE_NAME, UM_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_MODULE]') AND TYPE IN (N'U'))
CREATE TABLE UM_MODULE(
    UM_ID INTEGER IDENTITY(1,1),
    UM_MODULE_NAME VARCHAR(100),
    UNIQUE(UM_MODULE_NAME),
    PRIMARY KEY(UM_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_PERMISSION]') AND TYPE IN (N'U'))
CREATE TABLE UM_PERMISSION (
                    UM_ID INTEGER IDENTITY(1,1),
                    UM_RESOURCE_ID VARCHAR(255) NOT NULL,
                    UM_ACTION VARCHAR(255) NOT NULL,
                    UM_TENANT_ID INTEGER DEFAULT 0,
                    UM_MODULE_ID INTEGER DEFAULT 0,
                UNIQUE(UM_RESOURCE_ID,UM_ACTION, UM_TENANT_ID),
                    PRIMARY KEY (UM_ID, UM_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_ROLE_PERMISSION]') AND TYPE IN (N'U'))
CREATE TABLE UM_ROLE_PERMISSION (
            UM_ID INTEGER IDENTITY(1,1),
                    UM_PERMISSION_ID INTEGER NOT NULL,
                    UM_ROLE_NAME VARCHAR(255) NOT NULL,
                    UM_IS_ALLOWED SMALLINT NOT NULL,
                    UM_TENANT_ID INTEGER DEFAULT 0,
                    UM_DOMAIN_ID INTEGER,
                    FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID),
                    FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID),
            --FOREIGN KEY (UM_ROLE_ID) REFERENCES UM_ROLE(UM_ID) ON DELETE CASCADE,                    
                    PRIMARY KEY (UM_ID, UM_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_USER_PERMISSION]') AND TYPE IN (N'U'))
CREATE TABLE UM_USER_PERMISSION (
                    UM_ID INTEGER IDENTITY(1,1),
                    UM_PERMISSION_ID INTEGER NOT NULL,
                    UM_USER_NAME VARCHAR(255) NOT NULL,
                    UM_IS_ALLOWED SMALLINT NOT NULL,
                    UM_TENANT_ID INTEGER DEFAULT 0,
                    UNIQUE (UM_PERMISSION_ID, UM_USER_NAME, UM_TENANT_ID),
                    FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID),
                    --FOREIGN KEY (UM_USER_ID) REFERENCES UM_USER(UM_ID) ON DELETE CASCADE,
                    PRIMARY KEY (UM_ID, UM_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_USER_ROLE]') AND TYPE IN (N'U'))
CREATE TABLE UM_USER_ROLE (
                    UM_ID INTEGER IDENTITY(1,1),
                    UM_ROLE_ID INTEGER NOT NULL,
                    UM_USER_ID INTEGER NOT NULL,
                    UM_TENANT_ID INTEGER DEFAULT 0,
                    UNIQUE (UM_USER_ID, UM_ROLE_ID, UM_TENANT_ID),
                    FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_ROLE(UM_ID, UM_TENANT_ID),
                    FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID),
                    PRIMARY KEY (UM_ID, UM_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_ACCOUNT_MAPPING]') AND TYPE IN (N'U'))
CREATE TABLE UM_ACCOUNT_MAPPING(
    UM_ID INTEGER IDENTITY(1,1),
    UM_USER_NAME VARCHAR(255) NOT NULL,
    UM_TENANT_ID INTEGER NOT NULL,
    UM_USER_STORE_DOMAIN VARCHAR(100),
    UM_ACC_LINK_ID INTEGER NOT NULL,
    UNIQUE(UM_USER_NAME, UM_TENANT_ID, UM_USER_STORE_DOMAIN, UM_ACC_LINK_ID),
    FOREIGN KEY (UM_TENANT_ID) REFERENCES UM_TENANT(UM_ID),
    PRIMARY KEY (UM_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_DIALECT]') AND TYPE IN (N'U'))
CREATE TABLE UM_DIALECT(
            UM_ID INTEGER IDENTITY(1,1), 
            UM_DIALECT_URI VARCHAR(255) NOT NULL,
            UM_TENANT_ID INTEGER DEFAULT 0,
            UNIQUE(UM_DIALECT_URI, UM_TENANT_ID),
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_CLAIM]') AND TYPE IN (N'U'))
CREATE TABLE UM_CLAIM(
            UM_ID INTEGER IDENTITY(1,1), 
            UM_DIALECT_ID INTEGER NOT NULL, 
            UM_CLAIM_URI VARCHAR(255) NOT NULL, 
            UM_DISPLAY_TAG VARCHAR(255), 
            UM_DESCRIPTION VARCHAR(255), 
        UM_MAPPED_ATTRIBUTE_DOMAIN VARCHAR(255),            
            UM_MAPPED_ATTRIBUTE VARCHAR(255), 
            UM_REG_EX VARCHAR(255), 
            UM_SUPPORTED SMALLINT, 
            UM_REQUIRED SMALLINT, 
            UM_DISPLAY_ORDER INTEGER,
            UM_TENANT_ID INTEGER DEFAULT 0,
            UM_CHECKED_ATTRIBUTE SMALLINT,
            UM_READ_ONLY SMALLINT,
            UNIQUE(UM_DIALECT_ID, UM_CLAIM_URI, UM_MAPPED_ATTRIBUTE_DOMAIN, UM_TENANT_ID),
            FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID), 
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_PROFILE_CONFIG]') AND TYPE IN (N'U'))
CREATE TABLE UM_PROFILE_CONFIG(
            UM_ID INTEGER IDENTITY(1,1), 
            UM_DIALECT_ID INTEGER, 
            UM_PROFILE_NAME VARCHAR(255), 
            UM_TENANT_ID INTEGER DEFAULT 0,
            FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID), 
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_CLAIM_BEHAVIOR]') AND TYPE IN (N'U'))
CREATE TABLE UM_CLAIM_BEHAVIOR(
            UM_ID INTEGER IDENTITY(1,1), 
            UM_PROFILE_ID INTEGER, 
            UM_CLAIM_ID INTEGER, 
            UM_BEHAVIOUR SMALLINT,
            UM_TENANT_ID INTEGER DEFAULT 0,
            FOREIGN KEY(UM_PROFILE_ID, UM_TENANT_ID) REFERENCES UM_PROFILE_CONFIG(UM_ID, UM_TENANT_ID), 
            FOREIGN KEY(UM_CLAIM_ID, UM_TENANT_ID) REFERENCES UM_CLAIM(UM_ID, UM_TENANT_ID), 
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_HYBRID_ROLE]') AND TYPE IN (N'U'))
CREATE TABLE UM_HYBRID_ROLE(
            UM_ID INTEGER NOT NULL IDENTITY(1,1),
            UM_ROLE_NAME VARCHAR(255) NOT NULL,
            UM_TENANT_ID INTEGER DEFAULT 0,
            PRIMARY KEY (UM_ID, UM_TENANT_ID),
            UNIQUE (UM_ROLE_NAME, UM_TENANT_ID)
);


IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_HYBRID_USER_ROLE]') AND TYPE IN (N'U'))
CREATE TABLE UM_HYBRID_USER_ROLE(
            UM_ID INTEGER NOT NULL IDENTITY(1,1),
            UM_USER_NAME VARCHAR(255),
            UM_ROLE_ID INTEGER NOT NULL,
            UM_TENANT_ID INTEGER DEFAULT 0,
        UM_DOMAIN_ID INTEGER,
            UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID, UM_DOMAIN_ID),
            FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID) ,
            FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID,UM_TENANT_ID),
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_HYBRID_REMEMBER_ME]') AND TYPE IN (N'U'))
CREATE TABLE UM_HYBRID_REMEMBER_ME(
            UM_ID INTEGER NOT NULL IDENTITY(1,1),
            UM_USER_NAME VARCHAR(255) NOT NULL,
            UM_COOKIE_VALUE VARCHAR(1024),
            UM_CREATED_TIME TIMESTAMP,
            UM_TENANT_ID INTEGER DEFAULT 0,
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_SYSTEM_ROLE]') AND TYPE IN (N'U'))
CREATE TABLE UM_SYSTEM_ROLE(
            UM_ID INTEGER NOT NULL IDENTITY(1,1),
            UM_ROLE_NAME VARCHAR(255) NOT NULL,
            UM_TENANT_ID INTEGER DEFAULT 0,
            PRIMARY KEY (UM_ID, UM_TENANT_ID),
            UNIQUE(UM_ROLE_NAME,UM_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_SYSTEM_USER_ROLE]') AND TYPE IN (N'U'))
CREATE TABLE UM_SYSTEM_USER_ROLE(
            UM_ID INTEGER IDENTITY(1,1),
            UM_USER_NAME VARCHAR(255),
            UM_ROLE_ID INTEGER NOT NULL,
            UM_TENANT_ID INTEGER DEFAULT 0,
            UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID),
            FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_SYSTEM_ROLE(UM_ID, UM_TENANT_ID),
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
);
---------------------------- End of tables that need to be edited --------------------------

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_CONTENT_HISTORY]') AND TYPE IN (N'U'))
CREATE TABLE REG_CONTENT_HISTORY (
             REG_CONTENT_ID INTEGER NOT NULL ,
             REG_CONTENT_DATA VARBINARY(MAX),
             REG_DELETED   SMALLINT,
             REG_TENANT_ID INTEGER DEFAULT 0,
             CONSTRAINT PK_REG_CONTENT_HISTORY PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_CLUSTER_LOCK]') AND TYPE IN (N'U'))
CREATE TABLE REG_CLUSTER_LOCK (
             REG_LOCK_NAME VARCHAR(20),
             REG_LOCK_STATUS VARCHAR(20),
             REG_LOCKED_TIME TIMESTAMP,
             REG_TENANT_ID INTEGER DEFAULT 0,
             CONSTRAINT PK_REG_CLUSTER_LOCK PRIMARY KEY (REG_LOCK_NAME)
);


CREATE INDEX REG_LOG_IND_BY_REGLOG ON REG_LOG(REG_LOGGED_TIME, REG_TENANT_ID);

CREATE INDEX REG_PATH_IND_BY_PATH_VALUE ON REG_PATH(REG_PATH_VALUE, REG_TENANT_ID);
CREATE INDEX REG_PATH_IND_BY_PARENT_ID ON REG_PATH(REG_PATH_PARENT_ID, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_IND_BY_NAME ON REG_RESOURCE(REG_NAME, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_IND_BY_PATH_ID ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_IND_BY_UUID ON REG_RESOURCE(REG_UUID);
CREATE INDEX REG_RESOURCE_IND_BY_TENAN ON REG_RESOURCE(REG_TENANT_ID, REG_UUID);
CREATE INDEX REG_RESOURCE_IND_BY_TYPE ON REG_RESOURCE(REG_TENANT_ID, REG_MEDIA_TYPE);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_HISTORY]') AND TYPE IN (N'U'))
CREATE TABLE REG_RESOURCE_HISTORY (
            REG_PATH_ID         INTEGER NOT NULL ,
            REG_NAME            VARCHAR(256),
            REG_VERSION         INTEGER NOT NULL,
            REG_MEDIA_TYPE      VARCHAR(500),
            REG_CREATOR         VARCHAR(31) NOT NULL,
            REG_CREATED_TIME    TIMESTAMP NOT NULL,
            REG_LAST_UPDATOR    VARCHAR(31),
            REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL,
            REG_DESCRIPTION     VARCHAR(1000),
            REG_CONTENT_ID      INTEGER,
            REG_DELETED         SMALLINT,
            REG_TENANT_ID INTEGER DEFAULT 0,
            REG_UUID VARCHAR(100) NOT NULL,
            FOREIGN KEY (REG_PATH_ID,REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID,REG_TENANT_ID),
            FOREIGN KEY (REG_CONTENT_ID,REG_TENANT_ID) REFERENCES REG_CONTENT_HISTORY (REG_CONTENT_ID,REG_TENANT_ID),            
            CONSTRAINT PK_REG_RESOURCE_HISTORY PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
);

CREATE INDEX REG_RES_HIST_IND_BY_NAME ON REG_RESOURCE_HISTORY(REG_NAME, REG_TENANT_ID);
CREATE INDEX REG_RES_HIST_IND_BY_PATH_ID ON REG_RESOURCE_HISTORY(REG_PATH_ID, REG_NAME, REG_TENANT_ID);



IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_COMMENT]') AND TYPE IN (N'U'))
CREATE TABLE REG_RESOURCE_COMMENT (
            REG_COMMENT_ID          INTEGER NOT NULL,
            REG_VERSION             INTEGER,
            REG_PATH_ID             INTEGER,
            REG_RESOURCE_NAME       VARCHAR(256),
            FOREIGN KEY (REG_PATH_ID,REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID,REG_TENANT_ID),
            FOREIGN KEY (REG_COMMENT_ID,REG_TENANT_ID) REFERENCES REG_COMMENT (REG_ID,REG_TENANT_ID),            
            REG_TENANT_ID INTEGER DEFAULT 0
); 

ALTER TABLE REG_RESOURCE_COMMENT ADD CONSTRAINT UNQ_REG_RESOURCE_COMMENT UNIQUE (REG_COMMENT_ID, REG_VERSION, REG_PATH_ID);
CREATE INDEX REG_RES_COMM_BY_PATH_ID ON REG_RESOURCE_COMMENT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
CREATE INDEX REG_RES_COMM_BY_VERSION ON REG_RESOURCE_COMMENT(REG_VERSION, REG_TENANT_ID);


IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_RATING]') AND TYPE IN (N'U'))
CREATE TABLE REG_RESOURCE_RATING (
            REG_RATING_ID           INTEGER NOT NULL ,
            REG_VERSION             INTEGER,
            REG_PATH_ID             INTEGER,
            REG_RESOURCE_NAME       VARCHAR(256),
            FOREIGN KEY (REG_PATH_ID,REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID,REG_TENANT_ID),
            FOREIGN KEY (REG_RATING_ID,REG_TENANT_ID) REFERENCES REG_RATING (REG_ID,REG_TENANT_ID),           
            REG_TENANT_ID INTEGER DEFAULT 0
);

ALTER TABLE REG_RESOURCE_RATING ADD CONSTRAINT UNQ_REG_RESOURCE_RATING UNIQUE (REG_RATING_ID, REG_VERSION, REG_PATH_ID);
CREATE INDEX REG_RATING_IND_BY_PATH_ID ON REG_RESOURCE_RATING(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
CREATE INDEX REG_RATING_IND_BY_VERSION ON REG_RESOURCE_RATING(REG_VERSION, REG_TENANT_ID);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_TAG]') AND TYPE IN (N'U'))
CREATE TABLE REG_RESOURCE_TAG (
            REG_TAG_ID              INTEGER NOT NULL IDENTITY(1,2),
            REG_VERSION             INTEGER,
            REG_PATH_ID             INTEGER,
            REG_RESOURCE_NAME       VARCHAR(256),
            FOREIGN KEY (REG_PATH_ID,REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID,REG_TENANT_ID),
            FOREIGN KEY (REG_TAG_ID,REG_TENANT_ID) REFERENCES REG_TAG (REG_ID,REG_TENANT_ID),            
            REG_TENANT_ID INTEGER DEFAULT 0
);

ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT UNQ_REG_RESOURCE_TAG UNIQUE (REG_TAG_ID, REG_VERSION, REG_PATH_ID);
CREATE INDEX REG_TAG_IND_BY_PATH_ID ON REG_RESOURCE_TAG(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
CREATE INDEX REG_TAG_IND_BY_VERSION ON REG_RESOURCE_TAG(REG_VERSION, REG_TENANT_ID);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_PROPERTY]') AND TYPE IN (N'U'))
CREATE TABLE REG_RESOURCE_PROPERTY (
            REG_PROPERTY_ID         INTEGER NOT NULL IDENTITY(1,2),
            REG_VERSION             INTEGER,
            REG_PATH_ID             INTEGER,
            REG_RESOURCE_NAME       VARCHAR(256),
            FOREIGN KEY (REG_PATH_ID,REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID,REG_TENANT_ID),
            FOREIGN KEY (REG_PROPERTY_ID,REG_TENANT_ID) REFERENCES REG_PROPERTY (REG_ID,REG_TENANT_ID),           
            REG_TENANT_ID INTEGER DEFAULT 0
);

ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT UNQ_RREG_RESOURCE_PROPERTY UNIQUE (REG_PROPERTY_ID, REG_VERSION, REG_PATH_ID);
CREATE INDEX REG_RESC_PROP_BY_VERN_PROPID ON REG_RESOURCE_PROPERTY(REG_PROPERTY_ID,REG_VERSION, REG_TENANT_ID);
CREATE INDEX REG_RESC_PROP_BY_VERN_PATHNAME ON REG_RESOURCE_PROPERTY(REG_PROPERTY_ID,REG_PATH_ID,REG_RESOURCE_NAME, REG_TENANT_ID);

CREATE INDEX REG_SNAPSHOT_PATH_ID ON REG_SNAPSHOT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
CREATE INDEX UM_USER_ID_INDEX ON UM_USER_ATTRIBUTE(UM_USER_ID);


IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_MODULE_ACTIONS]') AND TYPE IN (N'U'))
CREATE TABLE UM_MODULE_ACTIONS(
    UM_ACTION VARCHAR(255) NOT NULL,
    UM_MODULE_ID INTEGER NOT NULL,
    PRIMARY KEY(UM_ACTION, UM_MODULE_ID),
    FOREIGN KEY (UM_MODULE_ID) REFERENCES UM_MODULE(UM_ID)
);


IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_SHARED_USER_ROLE]') AND TYPE IN (N'U'))
CREATE TABLE UM_SHARED_USER_ROLE(
    UM_ROLE_ID INTEGER NOT NULL,
    UM_USER_ID INTEGER NOT NULL,
    UM_USER_TENANT_ID INTEGER NOT NULL,
    UM_ROLE_TENANT_ID INTEGER NOT NULL,
    UNIQUE(UM_USER_ID,UM_ROLE_ID,UM_USER_TENANT_ID, UM_ROLE_TENANT_ID),
    FOREIGN KEY(UM_ROLE_ID,UM_ROLE_TENANT_ID) REFERENCES UM_ROLE(UM_ID,UM_TENANT_ID) ,
    FOREIGN KEY(UM_USER_ID,UM_USER_TENANT_ID) REFERENCES UM_USER(UM_ID,UM_TENANT_ID)
);

CREATE INDEX UM_ROLE_NAME_IND ON UM_HYBRID_ROLE(UM_ROLE_NAME);
CREATE INDEX SYSTEM_ROLE_IND_BY_RN_TI ON UM_SYSTEM_ROLE(UM_ROLE_NAME, UM_TENANT_ID);

GO
-- Triggers Set -------------

CREATE OR ALTER TRIGGER TRG_DEL_UM_MODULE on UM_MODULE INSTEAD OF
DELETE
    AS 
   BEGIN
    DELETE UMA FROM DELETED, UM_MODULE_ACTIONS UMA WHERE UMA.UM_MODULE_ID = DELETED.UM_ID;
   END
   DELETE FROM UM_MODULE WHERE (UM_ID) IN (SELECT UM_ID FROM DELETED);
;
GO


CREATE OR ALTER TRIGGER TRG_DEL_UM_USER on UM_USER INSTEAD OF 
DELETE
    AS 
   BEGIN
      DELETE UUR FROM DELETED,UM_USER_ROLE UUR WHERE UUR.UM_USER_ID = DELETED.UM_ID and UUR.UM_TENANT_ID = DELETED.UM_TENANT_ID;
      DELETE USUR FROM DELETED,UM_SHARED_USER_ROLE USUR WHERE USUR.UM_USER_ID = DELETED.UM_ID and USUR.UM_USER_TENANT_ID = DELETED.UM_TENANT_ID;
      DELETE UMA FROM DELETED,UM_USER_ATTRIBUTE UMA WHERE UMA.UM_USER_ID = DELETED.UM_ID and UMA.UM_TENANT_ID = DELETED.UM_TENANT_ID;
   END
   DELETE FROM UM_USER WHERE ((UM_ID ) IN (SELECT UM_ID FROM DELETED)) AND ((UM_TENANT_ID) IN (SELECT UM_TENANT_ID FROM DELETED));
;
GO

CREATE OR ALTER TRIGGER TRG_DEL_UM_PERMISSION on UM_PERMISSION INSTEAD OF 
DELETE
    AS 
   BEGIN
      DELETE URP FROM DELETED,UM_ROLE_PERMISSION URP WHERE URP.UM_PERMISSION_ID = DELETED.UM_ID and URP.UM_TENANT_ID = DELETED.UM_TENANT_ID;
      DELETE UUP FROM DELETED,UM_USER_PERMISSION UUP WHERE UUP.UM_PERMISSION_ID = DELETED.UM_ID and UUP.UM_TENANT_ID = DELETED.UM_TENANT_ID;
   END
   DELETE FROM UM_PERMISSION WHERE ((UM_ID ) IN (SELECT UM_ID FROM DELETED)) AND ((UM_TENANT_ID) IN (SELECT UM_TENANT_ID FROM DELETED));
;
GO

CREATE OR ALTER TRIGGER TRG_DEL_DOMAIN on UM_DOMAIN INSTEAD OF 
DELETE
    AS 
   BEGIN
      DELETE UHUR FROM DELETED,UM_HYBRID_USER_ROLE UHUR WHERE UHUR.UM_DOMAIN_ID = DELETED.UM_DOMAIN_ID and UHUR.UM_TENANT_ID = DELETED.UM_TENANT_ID;
      DELETE URP FROM DELETED,UM_ROLE_PERMISSION URP WHERE URP.UM_DOMAIN_ID = DELETED.UM_DOMAIN_ID and URP.UM_TENANT_ID = DELETED.UM_TENANT_ID;
   END
   DELETE FROM UM_DOMAIN WHERE ((UM_ID ) IN (SELECT UM_ID FROM DELETED)) AND ((UM_TENANT_ID) IN (SELECT UM_TENANT_ID FROM DELETED));
;
GO

CREATE OR ALTER TRIGGER TRG_DEL_UM_ROLE on UM_ROLE INSTEAD OF 
DELETE
    AS 
   BEGIN
      DELETE UUR
      FROM
         DELETED,UM_USER_ROLE UUR 
      WHERE
         UUR.UM_ROLE_ID = DELETED.UM_ID 
         and UUR.UM_TENANT_ID = DELETED.UM_TENANT_ID;
DELETE USUR
FROM
   DELETED, UM_SHARED_USER_ROLE USUR 
WHERE
   USUR.UM_ROLE_ID = DELETED.UM_ID 
   and USUR.UM_ROLE_TENANT_ID = DELETED.UM_TENANT_ID;
   END
   DELETE FROM UM_ROLE WHERE ((UM_ID ) IN (SELECT UM_ID FROM DELETED)) AND ((UM_TENANT_ID) IN (SELECT UM_TENANT_ID FROM DELETED));
;
GO


CREATE OR ALTER TRIGGER TRG_DEL_UM_TENANT on UM_TENANT INSTEAD OF 
DELETE
    AS 
   BEGIN
      DELETE UAM
      FROM
         DELETED, UM_ACCOUNT_MAPPING UAM 
      WHERE
         UAM.UM_TENANT_ID = DELETED.UM_ID;
   END
   DELETE FROM UM_TENANT WHERE (UM_ID) IN (SELECT UM_ID FROM DELETED);
;
GO

CREATE OR ALTER TRIGGER TRG_DEL_HYBRID_ROLE on UM_HYBRID_ROLE INSTEAD OF 
DELETE
    AS 
   BEGIN
      DELETE UHUR
      FROM
         DELETED, UM_HYBRID_USER_ROLE UHUR 
      WHERE
         UHUR.UM_ROLE_ID = DELETED.UM_ID 
         and UHUR.UM_TENANT_ID = DELETED.UM_TENANT_ID;
   END
   DELETE FROM UM_HYBRID_ROLE WHERE ((UM_ID ) IN (SELECT UM_ID FROM DELETED)) AND ((UM_TENANT_ID) IN (SELECT UM_TENANT_ID FROM DELETED));
;
GO
